using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class CustomerDAO : ICustomerDAO
    {

        private const string SQL_SELECT_CUSTOMER = "SELECT * FROM Customers";

        private const string SQL_SELECT_CUSTOMER_BY_ID = "SELECT * FROM Customers WHERE CustomerID=@CustomerID";

        private const string SQL_SELECT_CUSTOMER_BY_USERNAME = "SELECT * FROM Customers WHERE Username=@Username";

        private const string SQL_INSERT_CUSTOMER = "INSERT INTO Customers " +
                        "(Username, FirstName, LastName, DayOfBirth, Email, Address, City, Phone, BankName, AccountNumber, Taxcode) " +
                        "VALUES (@Username, @FirstName, @LastName, @DayOfBirth, @Email, @Address, @City, @Phone, @BankName, @AccountNumber, @Taxcode)";

        private const string SQL_UPDATE_CUSTOMER = "UPDATE Customers SET " +
                        "Email=@Email, Address=@Address, City=@City, Phone=@Phone, BankName=@BankName, AccountNumber=@AccountNumber, Taxcode=@Taxcode " +
                        "WHERE Username=@Username";

        private const string SQL_DELETE_CUSTOMER = "DELETE FROM Customers WHERE CustomerID=@CustomerID";

        private const string PARAM_USER_NAME = "@Username";
        private const string PARAM_FIRST_NAME = "@FirstName";
        private const string PARAM_LAST_NAME = "@LastName";
        private const string PARAM_BIRTH_DAY = "@DayOfBirth";
        private const string PARAM_EMAIL = "@Email";
        private const string PARAM_ADDRESS = "@Address";
        private const string PARAM_CITY = "@City";
        private const string PARAM_PHONE = "@Phone";
        private const string PARAM_BANK_NAME = "@BankName";
        private const string PARAM_ACCOUNT_NUMBER = "@AccountNumber";
        private const string PARAM_TAX_CODE= "@Taxcode";
        private const string PARAM_CUSTOMER_ID = "@CustomerID";

        #region ICustomerDAO Members

        public bool Delete(CustomerInfo cust)
        {
            SqlParameter parm = new SqlParameter(PARAM_CUSTOMER_ID, SqlDbType.Int);
            parm.Value = cust.CustomerID;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_DELETE_CUSTOMER, parm);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<CustomerInfo> GetAll()
        {
            IList<CustomerInfo> customers = new List<CustomerInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_CUSTOMER, null))
            {
                while (reader.Read())
                {
                    CustomerInfo cust = new CustomerInfo();
                    cust.CustomerID = reader.GetInt32(0);
                    cust.UserName = reader.GetString(1);
                    cust.FirstName = reader.GetString(2);
                    cust.LastName = reader.GetString(3);
                    cust.DateOfBirth = reader.GetDateTime(4);
                    cust.Email = reader.GetString(5);
                    cust.Address = reader.GetString(6);
                    cust.City = reader.GetString(7);
                    cust.Phone = reader.GetString(8);
                    cust.BankName = reader.GetString(9);
                    cust.AccountNumber = reader.GetString(10);
                    if (!reader.IsDBNull(11))
                        cust.TaxCode = reader.GetString(11);
                    cust.Orders = new OrderDAO().GetByUserName(cust.UserName);
                    customers.Add(cust);
                }
            }
            return customers;
        }

        public CustomerInfo GetByID(string customerID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_CUSTOMER_ID, SqlDbType.Int) };
            parms[0].Value = int.Parse(customerID);

            CustomerInfo cust = new CustomerInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_CUSTOMER_BY_ID, parms))
            {
                while (reader.Read())
                {
                    cust.CustomerID = reader.GetInt32(0);
                    cust.UserName = reader.GetString(1);
                    cust.FirstName = reader.GetString(2);
                    cust.LastName = reader.GetString(3);
                    cust.DateOfBirth = reader.GetDateTime(4);
                    cust.Email = reader.GetString(5);
                    cust.Address = reader.GetString(6);
                    cust.City = reader.GetString(7);
                    cust.Phone = reader.GetString(8);
                    cust.BankName = reader.GetString(9);
                    cust.AccountNumber = reader.GetString(10);
                    if (!reader.IsDBNull(11))
                        cust.TaxCode = reader.GetString(11);
                    cust.Orders = new OrderDAO().GetByUserName(cust.UserName);
                }
            }
            return cust;
        }

        public CustomerInfo GetByUserName(string username)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };
            parms[0].Value = username;

            CustomerInfo cust = new CustomerInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_CUSTOMER_BY_USERNAME, parms))
            {
                while (reader.Read())
                {
                    cust.CustomerID = reader.GetInt32(0);
                    cust.UserName = reader.GetString(1);
                    cust.FirstName = reader.GetString(2);
                    cust.LastName = reader.GetString(3);
                    cust.DateOfBirth = reader.GetDateTime(4);
                    cust.Email = reader.GetString(5);
                    cust.Address = reader.GetString(6);
                    cust.City = reader.GetString(7);
                    cust.Phone = reader.GetString(8);
                    cust.BankName = reader.GetString(9);
                    cust.AccountNumber = reader.GetString(10);
                    if (!reader.IsDBNull(11))
                        cust.TaxCode = reader.GetString(11);
                    cust.Orders = new OrderDAO().GetByUserName(cust.UserName);
                }
            }
            return cust;
        }

        public bool Insert(CustomerInfo cust)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar),
                new SqlParameter(PARAM_FIRST_NAME, SqlDbType.NVarChar),
                new SqlParameter(PARAM_LAST_NAME, SqlDbType.NVarChar),
                new SqlParameter(PARAM_BIRTH_DAY, SqlDbType.DateTime),
                new SqlParameter(PARAM_EMAIL, SqlDbType.NVarChar),
                new SqlParameter(PARAM_ADDRESS, SqlDbType.NVarChar),
                new SqlParameter(PARAM_CITY, SqlDbType.NVarChar),
                new SqlParameter(PARAM_PHONE, SqlDbType.NVarChar),
                new SqlParameter(PARAM_BANK_NAME, SqlDbType.NVarChar),
                new SqlParameter(PARAM_ACCOUNT_NUMBER, SqlDbType.NVarChar),
                new SqlParameter(PARAM_TAX_CODE, SqlDbType.NChar)};

            parms[0].Value = cust.UserName;
            parms[1].Value = cust.FirstName;
            parms[2].Value = cust.LastName;
            parms[3].Value = cust.DateOfBirth;
            parms[4].Value = cust.Email;
            parms[5].Value = cust.Address;
            parms[6].Value = cust.City;
            parms[7].Value = cust.Phone;
            parms[8].Value = cust.BankName;
            parms[9].Value = cust.AccountNumber;
            parms[10].Value = cust.TaxCode;

            try
            {
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                int result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CUSTOMER, parms);
                SQLDBHelper.SetNewIdentity(conn, cust.CustomerID);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool Update(CustomerInfo cust)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter(PARAM_EMAIL, SqlDbType.NVarChar),
                new SqlParameter(PARAM_ADDRESS, SqlDbType.NVarChar),
                new SqlParameter(PARAM_CITY, SqlDbType.NVarChar),
                new SqlParameter(PARAM_PHONE, SqlDbType.NVarChar),
                new SqlParameter(PARAM_BANK_NAME, SqlDbType.NVarChar),
                new SqlParameter(PARAM_ACCOUNT_NUMBER, SqlDbType.NVarChar), 
                new SqlParameter(PARAM_TAX_CODE, SqlDbType.NChar), 
                new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };

            parms[0].Value = cust.Email;
            parms[1].Value = cust.Address;
            parms[2].Value = cust.City;
            parms[3].Value = cust.Phone;
            parms[4].Value = cust.BankName;
            parms[5].Value = cust.AccountNumber;
            parms[6].Value = cust.TaxCode;
            parms[7].Value = cust.UserName;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_CUSTOMER, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        #endregion
    }
}
